package zy.dao.shop.want.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.shop.want.WantReportDAO;
import zy.dto.shop.want.WantDetailReportDto;
import zy.dto.shop.want.WantReportDto;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class WantReportDAOImpl extends BaseDaoImpl implements WantReportDAO{

	private String getWantReportSQL(Map<String, Object> params){
		StringBuffer sql = new StringBuffer();
		String bd_code = StringUtil.trimString(params.get("bd_code"));
		String tp_code = StringUtil.trimString(params.get("tp_code"));
		String wt_shop_code = StringUtil.trimString(params.get("wt_shop_code"));
		if(StringUtil.isNotEmpty(bd_code)){
			params.put("bdCodes", Arrays.asList(bd_code.split(",")));
			sql.append(" AND pd_bd_code IN(:bdCodes)");
		}
		if(StringUtil.isNotEmpty(tp_code)){
			params.put("tpCodes", Arrays.asList(tp_code.split(",")));
			sql.append(" AND pd_tp_code IN(:tpCodes)");
		}
		if(StringUtil.isNotEmpty(wt_shop_code)){
			params.put("shop_codes", Arrays.asList(wt_shop_code.split(",")));
			sql.append(" AND wt_shop_code IN (:shop_codes) ");
		}
		if(StringUtil.isNotEmpty(params.get("wt_outdp_code"))){
			sql.append(" AND wt_outdp_code = :wt_outdp_code ");
		}
		if(StringUtil.isNotEmpty(params.get("wt_indp_code"))){
			sql.append(" AND wt_indp_code = :wt_indp_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_season"))){
			sql.append(" AND pd_season = :pd_season ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_year"))){
			sql.append(" AND pd_year = :pd_year ");
		}
		if(StringUtil.isNotEmpty(params.get("wt_manager"))){
			sql.append(" AND wt_manager = :wt_manager ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_code"))){
			sql.append(" AND pd_code = :pd_code ");
		}
		if(StringUtil.isNotEmpty(params.get("pd_no"))){
			sql.append(" AND INSTR(pd_no,:pd_no) > 0 ");
		}
		if(StringUtil.isNotEmpty(params.get("begindate"))){
			sql.append(" AND wt_ar_date >= :begindate ");
		}
		if(StringUtil.isNotEmpty(params.get("enddate"))){
			sql.append(" AND wt_ar_date <= :enddate ");
		}
		if(StringUtil.isNotEmpty(params.get("wt_type"))){
			sql.append(" AND wt_type = :wt_type ");
		}
		if(StringUtil.isNotEmpty(params.get("wt_ar_state"))){
			sql.append(" AND wt_ar_state = :wt_ar_state ");
		}else {
			sql.append(" AND wt_ar_state IN (3,4,5)");
		}
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}
	
	@Override
	public Integer countWantReport(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1) FROM");
		sql.append(" (SELECT 1 FROM t_shop_want t");
		sql.append(" JOIN t_shop_wantlist wtl ON wtl_number = wt_number AND wtl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = wtl_pd_code AND pd.companyid = wtl.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWantReportSQL(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code");
		}else if("shop".equals(type)){
			sql.append(" GROUP BY wt_shop_code");
		}else if("product".equals(type)){
			sql.append(" GROUP BY wtl_pd_code");
		}else if("season".equals(type)){
			sql.append(" GROUP BY pd_season");
		}else if("depot".equals(type)){
			sql.append(" GROUP BY wt_outdp_code,wt_indp_code");
		}else if("manager".equals(type)){
			sql.append(" GROUP BY wt_manager");
		}
		sql.append(")temp");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public Map<String, Object> sumWantReport(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" SUM(IF(wt_type = 0,wtl_applyamount,0)) AS apply_amount,");
		sql.append(" SUM(IF(wt_type = 0,wtl_sendamount,0)) AS send_amount,");
		sql.append(" SUM(IF(wt_type = 1,-wtl_applyamount,0)) AS apply_amount_th,");
		sql.append(" SUM(IF(wt_type = 1,-wtl_sendamount,0)) AS send_amount_th,");
		sql.append(" SUM(IF(wt_type = 0,wtl_applyamount*wtl_unitprice,0)) AS apply_money,");
		sql.append(" SUM(IF(wt_type = 0,wtl_sendamount*wtl_unitprice,0)) AS send_money,");
		sql.append(" SUM(IF(wt_type = 0,wtl_sendamount*wtl_costprice,0)) AS send_cost,");
		sql.append(" SUM(IF(wt_type = 1,-wtl_applyamount*wtl_unitprice,0)) AS apply_money_th,");
		sql.append(" SUM(IF(wt_type = 1,-wtl_sendamount*wtl_unitprice,0)) AS send_money_th,");
		sql.append(" SUM(IF(wt_type = 1,-wtl_sendamount*wtl_costprice,0)) AS send_cost_th");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_shop_wantlist wtl ON wtl_number = wt_number AND wtl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = wtl_pd_code AND pd.companyid = wtl.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWantReportSQL(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<WantReportDto> listWantReport(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		String type = StringUtil.trimString(params.get("type"));
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT wtl_id AS id,");
		sql.append(" SUM(IF(wt_type = 0,wtl_applyamount,0)) AS apply_amount,");
		sql.append(" SUM(IF(wt_type = 0,wtl_sendamount,0)) AS send_amount,");
		sql.append(" SUM(IF(wt_type = 1,-wtl_applyamount,0)) AS apply_amount_th,");
		sql.append(" SUM(IF(wt_type = 1,-wtl_sendamount,0)) AS send_amount_th,");
		sql.append(" SUM(IF(wt_type = 0,wtl_applyamount*wtl_unitprice,0)) AS apply_money,");
		sql.append(" SUM(IF(wt_type = 0,wtl_sendamount*wtl_unitprice,0)) AS send_money,");
		sql.append(" SUM(IF(wt_type = 0,wtl_sendamount*wtl_costprice,0)) AS send_cost,");
		sql.append(" SUM(IF(wt_type = 1,-wtl_applyamount*wtl_unitprice,0)) AS apply_money_th,");
		sql.append(" SUM(IF(wt_type = 1,-wtl_sendamount*wtl_unitprice,0)) AS send_money_th,");
		sql.append(" SUM(IF(wt_type = 1,-wtl_sendamount*wtl_costprice,0)) AS send_cost_th,");
		if("brand".equals(type)){
			sql.append(" pd_bd_code AS code,");
			sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS name");
		}else if("type".equals(type)){
			sql.append(" pd_tp_code AS code,");
			sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS name");
		}else if("shop".equals(type)){
			sql.append(" wt_shop_code AS code,sp_name AS name");
		}else if("product".equals(type)){
			sql.append(" wtl_pd_code AS code,pd_name AS name,pd_no");
		}else if("season".equals(type)){
			sql.append(" pd_season AS code,pd_season AS name");
		}else if("depot".equals(type)){
			sql.append(" wt_outdp_code AS code,wt_indp_code AS indp_code,");
			sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = wt_outdp_code AND dp.companyid = t.companyid LIMIT 1) AS name,");
			sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = wt_indp_code AND dp.companyid = t.companyid LIMIT 1) AS indp_name");
		}else if("manager".equals(type)){
			sql.append(" wt_manager AS code,wt_manager AS name");
		}
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_shop_wantlist wtl ON wtl_number = wt_number AND wtl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = wtl_pd_code AND pd.companyid = wtl.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWantReportSQL(params));
		if("brand".equals(type)){
			sql.append(" GROUP BY pd_bd_code");
		}else if("type".equals(type)){
			sql.append(" GROUP BY pd_tp_code");
		}else if("shop".equals(type)){
			sql.append(" GROUP BY wt_shop_code");
		}else if("product".equals(type)){
			sql.append(" GROUP BY wtl_pd_code");
		}else if("season".equals(type)){
			sql.append(" GROUP BY pd_season");
		}else if("depot".equals(type)){
			sql.append(" GROUP BY wt_outdp_code,wt_indp_code");
		}else if("manager".equals(type)){
			sql.append(" GROUP BY wt_manager");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(WantReportDto.class));
	}

	@Override
	public Integer countWantDetailReport(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_shop_wantlist wtl ON wtl_number = wt_number AND wtl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = wtl_pd_code AND pd.companyid = wtl.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWantReportSQL(params));
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public Map<String, Object> sumWantDetailReport(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append(" SUM(wtl_applyamount) AS wtl_applyamount,");
		sql.append(" SUM(wtl_sendamount) AS wtl_sendamount,");
		sql.append(" SUM(wtl_applyamount*wtl_unitprice) AS wtl_applymoney,");
		sql.append(" SUM(wtl_sendamount*wtl_unitprice) AS wtl_sendmoney,");
		sql.append(" SUM(wtl_sendamount*wtl_costprice) AS wtl_sendcost");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_shop_wantlist wtl ON wtl_number = wt_number AND wtl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = wtl_pd_code AND pd.companyid = wtl.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWantReportSQL(params));
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<WantDetailReportDto> listWantDetailReport(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT wtl_id,wt_date,wtl_number,wtl_pd_code,wtl_sub_code,wtl_sz_code,wtl_szg_code,wtl_cr_code,wtl_br_code,wtl_applyamount,wtl_sendamount,");
		sql.append(" wtl_unitprice,wtl_costprice,wtl_remark,wtl_type,t.companyid,pd_no,pd_name,pd_unit,pd_season,pd_year,");
		sql.append(" sp_name AS shop_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = wt_outdp_code AND dp.companyid = t.companyid LIMIT 1) AS outdp_name,");
		sql.append(" (SELECT dp_name FROM t_base_depot dp WHERE dp_code = wt_indp_code AND dp.companyid = t.companyid LIMIT 1) AS indp_name,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = wtl_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = wtl_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = wtl_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT bd_name FROM t_base_brand bd WHERE bd_code = pd_bd_code AND bd.companyid = t.companyid LIMIT 1) AS bd_name,");
		sql.append(" (SELECT tp_name FROM t_base_type tp WHERE tp_code = pd_tp_code AND tp.companyid = t.companyid LIMIT 1) AS tp_name");
		sql.append(" FROM t_shop_want t");
		sql.append(" JOIN t_shop_wantlist wtl ON wtl_number = wt_number AND wtl.companyid = t.companyid");
		sql.append(" JOIN t_base_product pd ON pd_code = wtl_pd_code AND pd.companyid = wtl.companyid");
		sql.append(" JOIN t_base_shop sp ON sp_code = wt_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(getWantReportSQL(params));
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(WantDetailReportDto.class));
	}

}
